{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# pyjanitor Usage Walkthrough"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`pyjanitor` is a Python-based API on top of `pandas` inspired by the [janitor](https://github.com/sfirke/janitor) R package. It aims to provide a clean, understandable interface based on method chaining for common and less-common tasks involving data cleaning and `DataFrame` manipulation. \n",
    "\n",
    "The core philosophy and augmentations on `pandas`' approach to data cleaning and `DataFrame` manipulation include:\n",
    "\n",
    "- A method-chaining paradigm for coding efficiency & clarity of code through greatly improved readability.\n",
    "- Implementation of common, useful `DataFrame` manipulation tasks that saves on repetitive code.\n",
    "- Focus on active tense / verb approaches to function naming to provide at-a-glance understanding of a data manipulation pipeline.\n",
    "\n",
    "## Why `pyjanitor`?\n",
    "\n",
    "Originally a simple port of the R package, `pyjanitor` has evolved from a set of convenient data cleaning routines into an experiment with the method-chaining paradigm.\n",
    "\n",
    "Data preprocessing is best expressed as a [directed acyclic graph (DAG)](https://en.wikipedia.org/wiki/Directed_acyclic_graph) of actions taken on data. We take a base data file as the starting point and perform actions on it such as removing null/empty rows, replacing them with other values, adding/renaming/removing columns of data, filtering rows, and more.\n",
    "\n",
    "The `pandas` API has been invaluable for the Python data science ecosystem and implements method chaining for a subset of methods as part of the API. For example, resetting indexes (`.reset_index()`), dropping null values (`.dropna()`), and more are accomplished via the appropriate `pd.DataFrame` method calls.\n",
    "\n",
    "Inspired by the R statistical language ecosystem, where consistent and good API design in the `dplyr` package enables end-users, who are not necessarily developers, to concisely express data processing code, `pyjanitor` has evolved into a language for expressing the data processing DAG for `pandas` users.\n",
    "\n",
    "## What is method chaining?\n",
    "\n",
    "To accomplish these goals, actions for which we would need to invoke imperative-style statements can be replaced with method chains that allow the user to read off the logical order of actions taken. Note the annotated example below. First, we introduce the textual description of a sample data cleaning pipeline:\n",
    "\n",
    "- Create `DataFrame`.\n",
    "- Delete one column.\n",
    "- Drop rows with empty values in two particular columns.\n",
    "- Rename another two columns.\n",
    "- Add a new column.\n",
    "- Reset index to account for the missing row we removed above\n",
    "\n",
    "In `pandas` code, this would look as such:\n",
    "\n",
    "```python\n",
    "df = pd.DataFrame(...)  # create a pandas DataFrame somehow.\n",
    "del df['column1']  # delete a column from the dataframe.\n",
    "df = df.dropna(subset=['column2', 'column3'])  # drop rows that have empty values in column 2 and 3.\n",
    "df = df.rename({'column2': 'unicorns', 'column3': 'dragons'})  # rename column2 and column3\n",
    "df['new_column'] = ['iterable', 'of', 'items']  # add a new column.\n",
    "df.reset_index(inplace=True, drop=True)  # reset index to account for the missing row we removed above\n",
    "```\n",
    "\n",
    "## The `pyjanitor` approach\n",
    "\n",
    "With `pyjanitor`, we enable method chaining with method names that are _verbs_ which describe the action taken:\n",
    "\n",
    "```python\n",
    "df = (\n",
    "    pd.DataFrame(...)\n",
    "    .remove_columns(['column1'])\n",
    "    .dropna(subset=['column2', 'column3'])\n",
    "    .rename_column('column2', 'unicorns')\n",
    "    .rename_column('column3', 'dragons')\n",
    "    .add_column('new_column', ['iterable', 'of', 'items'])\n",
    "    .reset_index(drop=True)\n",
    ")\n",
    "```\n",
    "\n",
    "We believe the `pyjanitor` chaining-based approach leads to much cleaner code where the intent of a series of `DataFrame` manipulations is much more immediately clear.\n",
    "\n",
    "`pyjanitor`’s etymology has a two-fold relationship to “cleanliness”. Firstly, it’s about extending `pandas` with convenient data cleaning routines. Secondly, it’s about providing a cleaner, method-chaining, verb-based API for common `pandas` routines.\n",
    "\n",
    "## A survey of `pyjanitor` functions\n",
    "\n",
    "- Cleaning column names (multi-indexes are possible!)\n",
    "- Removing empty rows and columns\n",
    "- Identifying duplicate entries\n",
    "- Encoding columns as categorical\n",
    "- Splitting your data into features and targets (for machine learning)\n",
    "- Adding, removing, and renaming columns\n",
    "- Coalesce multiple columns into a single column\n",
    "- Convert excel date (serial format) into a Python datetime format\n",
    "- Expand a single column that has delimited, categorical values into dummy-encoded variables\n",
    "\n",
    "A full list of functionality that `pyjanitor` implements can be found in the [API docs](https://ericmjl.github.io/pyjanitor/).\n",
    "\n",
    "## Some things that are different\n",
    "\n",
    "Some `pyjanitor` methods are `DataFrame`-mutating operations, i.e., in place. Given that in a method-chaining paradigm, `DataFrame`s that would be created at each step of the chain cannot be accessed anyway, duplication of data at each step would lead to unnecessary, potential considerable slowdowns and increased memory usage due to data-copying operations. The severity of such copying scales with `DataFrame` size. Take care to understand which functions change the original `DataFrame` you are chaining on if it is necessary to preserve that data. If it is, you can simply `.copy()` it as the first step in a `df.copy().operation1().operation2()...` chain.\n",
    "\n",
    "## How it works\n",
    "\n",
    "`pyjanitor` relies on the [Pandas Flavor](https://github.com/Zsailer/pandas_flavor) package to register new functions as object methods that can be called directly on `DataFrame`s. For example:\n",
    "\n",
    "```python\n",
    "import pandas as pd\n",
    "import pandas_flavor as pf\n",
    "\n",
    "\n",
    "@pf.register_dataframe_method\n",
    "def remove_column(df, column_name: str):\n",
    "    del df[column_name]\n",
    "    return df\n",
    "\n",
    "df = (\n",
    "    pd.read_csv('my_data.csv')\n",
    "    .remove_column('my_column_name')\n",
    "    .operation2(...)\n",
    ")\n",
    "```\n",
    "\n",
    "Importing the `janitor` package immediately registers these functions. The fact that each `DataFrame` method `pyjanitor` registers returns the `DataFrame` is what gives it the capability to method chain. \n",
    "\n",
    "Note that `pyjanitor` explicitly does not modify any existing `pandas` methods / functionality."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Demo of various `DataFrame` manipulation tasks using `pyjanitor`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we'll walk through some useful `pyjanitor`-based approaches to cleaning and manipulating `DataFrame`s."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Code preamble:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's take a look at our dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>First Name</th>\n      <th>Last Name</th>\n      <th>Employee Status</th>\n      <th>Subject</th>\n      <th>Hire Date</th>\n      <th>% Allocated</th>\n      <th>Full time?</th>\n      <th>do not edit! ---&gt;</th>\n      <th>Certification</th>\n      <th>Certification.1</th>\n      <th>Certification.2</th>\n      <th>Unnamed: 11</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>PE</td>\n      <td>39690.0</td>\n      <td>0.75</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>Physical ed</td>\n      <td>Theater</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>Drafting</td>\n      <td>39690.0</td>\n      <td>0.25</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>Physical ed</td>\n      <td>Theater</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Alicia</td>\n      <td>Keys</td>\n      <td>Teacher</td>\n      <td>Music</td>\n      <td>37118.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>Instr. music</td>\n      <td>Vocal music</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Ada</td>\n      <td>Lovelace</td>\n      <td>Teacher</td>\n      <td>NaN</td>\n      <td>27515.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>PENDING</td>\n      <td>Computers</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Desus</td>\n      <td>Nice</td>\n      <td>Administration</td>\n      <td>Dean</td>\n      <td>41431.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>PENDING</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Physics</td>\n      <td>11037.0</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>Science 6-12</td>\n      <td>Physics</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Chemistry</td>\n      <td>11037.0</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>Science 6-12</td>\n      <td>Physics</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>James</td>\n      <td>Joyce</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>32994.0</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>English 6-12</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>Hedy</td>\n      <td>Lamarr</td>\n      <td>Teacher</td>\n      <td>Science</td>\n      <td>27919.0</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>PENDING</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>Carlos</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>Basketball</td>\n      <td>42221.0</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>Physical ed</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>Young</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>NaN</td>\n      <td>34700.0</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Political sci.</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>Micheal</td>\n      <td>Larsen</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>40071.0</td>\n      <td>0.80</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>Vocal music</td>\n      <td>English</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>14</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>15</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>16</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": [
       "      First Name Last Name Employee Status     Subject  Hire Date  \\\n",
       "0          Jason    Bourne         Teacher          PE    39690.0   \n",
       "1          Jason    Bourne         Teacher    Drafting    39690.0   \n",
       "2         Alicia      Keys         Teacher       Music    37118.0   \n",
       "3            Ada  Lovelace         Teacher         NaN    27515.0   \n",
       "4          Desus      Nice  Administration        Dean    41431.0   \n",
       "5   Chien-Shiung        Wu         Teacher     Physics    11037.0   \n",
       "6   Chien-Shiung        Wu         Teacher   Chemistry    11037.0   \n",
       "7            NaN       NaN             NaN         NaN        NaN   \n",
       "8          James     Joyce         Teacher     English    32994.0   \n",
       "9           Hedy    Lamarr         Teacher     Science    27919.0   \n",
       "10        Carlos    Boozer           Coach  Basketball    42221.0   \n",
       "11         Young    Boozer           Coach         NaN    34700.0   \n",
       "12       Micheal    Larsen         Teacher     English    40071.0   \n",
       "13           NaN       NaN             NaN         NaN        NaN   \n",
       "14           NaN       NaN             NaN         NaN        NaN   \n",
       "15           NaN       NaN             NaN         NaN        NaN   \n",
       "16           NaN       NaN             NaN         NaN        NaN   \n",
       "\n",
       "    % Allocated Full time?  do not edit! ---> Certification Certification.1  \\\n",
       "0          0.75        Yes                NaN   Physical ed         Theater   \n",
       "1          0.25        Yes                NaN   Physical ed         Theater   \n",
       "2          1.00        Yes                NaN  Instr. music     Vocal music   \n",
       "3          1.00        Yes                NaN       PENDING       Computers   \n",
       "4          1.00        Yes                NaN       PENDING             NaN   \n",
       "5          0.50        Yes                NaN  Science 6-12         Physics   \n",
       "6          0.50        Yes                NaN  Science 6-12         Physics   \n",
       "7           NaN        NaN                NaN           NaN             NaN   \n",
       "8          0.50         No                NaN           NaN    English 6-12   \n",
       "9          0.50         No                NaN       PENDING             NaN   \n",
       "10          NaN         No                NaN   Physical ed             NaN   \n",
       "11          NaN         No                NaN           NaN  Political sci.   \n",
       "12         0.80         No                NaN   Vocal music         English   \n",
       "13          NaN        NaN                NaN           NaN             NaN   \n",
       "14          NaN        NaN                NaN           NaN             NaN   \n",
       "15          NaN        NaN                NaN           NaN             NaN   \n",
       "16          NaN        NaN                NaN           NaN             NaN   \n",
       "\n",
       "    Certification.2  Unnamed: 11  \n",
       "0               NaN          NaN  \n",
       "1               NaN          NaN  \n",
       "2               NaN          NaN  \n",
       "3               NaN          NaN  \n",
       "4               NaN          NaN  \n",
       "5               NaN          NaN  \n",
       "6               NaN          NaN  \n",
       "7               NaN          NaN  \n",
       "8               NaN          NaN  \n",
       "9               NaN          NaN  \n",
       "10              NaN          NaN  \n",
       "11              NaN          NaN  \n",
       "12              NaN          NaN  \n",
       "13              NaN          NaN  \n",
       "14              NaN          NaN  \n",
       "15              NaN          NaN  \n",
       "16              NaN          NaN  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel(\"dirty_data.xlsx\", engine=\"openpyxl\")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that this dataset is dirty in a number of ways, including the following:\n",
    "\n",
    "  * Column names contain spaces, punctuation marks, and inconsistent casing\n",
    "  * One row (`7`) with completely missing data\n",
    "  * One column (`do not edit! --->`) with completely missing data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Clean up our data using a `pyjanitor` method-chaining pipeline\n",
    "\n",
    "Let's run through a demo `DataFrame` cleaning procedure:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>first_name</th>\n      <th>last_name</th>\n      <th>employee_status</th>\n      <th>subject</th>\n      <th>hire_date</th>\n      <th>percent_allocated</th>\n      <th>full_time</th>\n      <th>certification</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>PE</td>\n      <td>2008-08-30</td>\n      <td>0.75</td>\n      <td>Yes</td>\n      <td>Physical ed</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>Drafting</td>\n      <td>2008-08-30</td>\n      <td>0.25</td>\n      <td>Yes</td>\n      <td>Physical ed</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Alicia</td>\n      <td>Keys</td>\n      <td>Teacher</td>\n      <td>Music</td>\n      <td>2001-08-15</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>Instr. music</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Ada</td>\n      <td>Lovelace</td>\n      <td>Teacher</td>\n      <td>NaN</td>\n      <td>1975-05-01</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>PENDING</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Desus</td>\n      <td>Nice</td>\n      <td>Administration</td>\n      <td>Dean</td>\n      <td>2013-06-06</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>PENDING</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Physics</td>\n      <td>1930-03-20</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>Science 6-12</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Chemistry</td>\n      <td>1930-03-20</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>Science 6-12</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>James</td>\n      <td>Joyce</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>1990-05-01</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>English 6-12</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>Hedy</td>\n      <td>Lamarr</td>\n      <td>Teacher</td>\n      <td>Science</td>\n      <td>1976-06-08</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>PENDING</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>Carlos</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>Basketball</td>\n      <td>2015-08-05</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>Physical ed</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>Young</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>NaN</td>\n      <td>1995-01-01</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>Political sci.</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>Micheal</td>\n      <td>Larsen</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>2009-09-15</td>\n      <td>0.80</td>\n      <td>No</td>\n      <td>Vocal music</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": [
       "      first_name last_name employee_status     subject  hire_date  \\\n",
       "0          Jason    Bourne         Teacher          PE 2008-08-30   \n",
       "1          Jason    Bourne         Teacher    Drafting 2008-08-30   \n",
       "2         Alicia      Keys         Teacher       Music 2001-08-15   \n",
       "3            Ada  Lovelace         Teacher         NaN 1975-05-01   \n",
       "4          Desus      Nice  Administration        Dean 2013-06-06   \n",
       "5   Chien-Shiung        Wu         Teacher     Physics 1930-03-20   \n",
       "6   Chien-Shiung        Wu         Teacher   Chemistry 1930-03-20   \n",
       "7          James     Joyce         Teacher     English 1990-05-01   \n",
       "8           Hedy    Lamarr         Teacher     Science 1976-06-08   \n",
       "9         Carlos    Boozer           Coach  Basketball 2015-08-05   \n",
       "10         Young    Boozer           Coach         NaN 1995-01-01   \n",
       "11       Micheal    Larsen         Teacher     English 2009-09-15   \n",
       "\n",
       "    percent_allocated full_time   certification  \n",
       "0                0.75       Yes     Physical ed  \n",
       "1                0.25       Yes     Physical ed  \n",
       "2                1.00       Yes    Instr. music  \n",
       "3                1.00       Yes         PENDING  \n",
       "4                1.00       Yes         PENDING  \n",
       "5                0.50       Yes    Science 6-12  \n",
       "6                0.50       Yes    Science 6-12  \n",
       "7                0.50        No    English 6-12  \n",
       "8                0.50        No         PENDING  \n",
       "9                 NaN        No     Physical ed  \n",
       "10                NaN        No  Political sci.  \n",
       "11               0.80        No     Vocal music  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_df = (\n",
    "    pd.read_excel(\"dirty_data.xlsx\", engine=\"openpyxl\")\n",
    "    .clean_names()\n",
    "    .remove_empty()\n",
    "    .rename_column(\"%_allocated\", \"percent_allocated\")\n",
    "    .rename_column(\"full_time_\", \"full_time\")\n",
    "    .coalesce([\"certification\", \"certification_1\"], \"certification\")\n",
    "    .encode_categorical([\"subject\", \"employee_status\", \"full_time\"])\n",
    "    .convert_excel_date(\"hire_date\")\n",
    "    .reset_index(drop=True)\n",
    ")\n",
    "\n",
    "cleaned_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The cleaned `DataFrame` looks much better and quite a bit more usable for our downstream tasks."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step-by-step walkthrough of `pyjanitor` `DataFrame` manipulations\n",
    "\n",
    "Just for clearer understanding of the above, let's see how `pyjanitor` progressively modified the data.\n",
    "\n",
    "Loading data in:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>First Name</th>\n      <th>Last Name</th>\n      <th>Employee Status</th>\n      <th>Subject</th>\n      <th>Hire Date</th>\n      <th>% Allocated</th>\n      <th>Full time?</th>\n      <th>do not edit! ---&gt;</th>\n      <th>Certification</th>\n      <th>Certification.1</th>\n      <th>Certification.2</th>\n      <th>Unnamed: 11</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>PE</td>\n      <td>39690.0</td>\n      <td>0.75</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>Physical ed</td>\n      <td>Theater</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>Drafting</td>\n      <td>39690.0</td>\n      <td>0.25</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>Physical ed</td>\n      <td>Theater</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Alicia</td>\n      <td>Keys</td>\n      <td>Teacher</td>\n      <td>Music</td>\n      <td>37118.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>Instr. music</td>\n      <td>Vocal music</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Ada</td>\n      <td>Lovelace</td>\n      <td>Teacher</td>\n      <td>NaN</td>\n      <td>27515.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>PENDING</td>\n      <td>Computers</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Desus</td>\n      <td>Nice</td>\n      <td>Administration</td>\n      <td>Dean</td>\n      <td>41431.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>PENDING</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Physics</td>\n      <td>11037.0</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>Science 6-12</td>\n      <td>Physics</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Chemistry</td>\n      <td>11037.0</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>Science 6-12</td>\n      <td>Physics</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>James</td>\n      <td>Joyce</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>32994.0</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>English 6-12</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>Hedy</td>\n      <td>Lamarr</td>\n      <td>Teacher</td>\n      <td>Science</td>\n      <td>27919.0</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>PENDING</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>Carlos</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>Basketball</td>\n      <td>42221.0</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>Physical ed</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>Young</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>NaN</td>\n      <td>34700.0</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Political sci.</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>Micheal</td>\n      <td>Larsen</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>40071.0</td>\n      <td>0.80</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>Vocal music</td>\n      <td>English</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>14</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>15</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>16</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": [
       "      First Name Last Name Employee Status     Subject  Hire Date  \\\n",
       "0          Jason    Bourne         Teacher          PE    39690.0   \n",
       "1          Jason    Bourne         Teacher    Drafting    39690.0   \n",
       "2         Alicia      Keys         Teacher       Music    37118.0   \n",
       "3            Ada  Lovelace         Teacher         NaN    27515.0   \n",
       "4          Desus      Nice  Administration        Dean    41431.0   \n",
       "5   Chien-Shiung        Wu         Teacher     Physics    11037.0   \n",
       "6   Chien-Shiung        Wu         Teacher   Chemistry    11037.0   \n",
       "7            NaN       NaN             NaN         NaN        NaN   \n",
       "8          James     Joyce         Teacher     English    32994.0   \n",
       "9           Hedy    Lamarr         Teacher     Science    27919.0   \n",
       "10        Carlos    Boozer           Coach  Basketball    42221.0   \n",
       "11         Young    Boozer           Coach         NaN    34700.0   \n",
       "12       Micheal    Larsen         Teacher     English    40071.0   \n",
       "13           NaN       NaN             NaN         NaN        NaN   \n",
       "14           NaN       NaN             NaN         NaN        NaN   \n",
       "15           NaN       NaN             NaN         NaN        NaN   \n",
       "16           NaN       NaN             NaN         NaN        NaN   \n",
       "\n",
       "    % Allocated Full time?  do not edit! ---> Certification Certification.1  \\\n",
       "0          0.75        Yes                NaN   Physical ed         Theater   \n",
       "1          0.25        Yes                NaN   Physical ed         Theater   \n",
       "2          1.00        Yes                NaN  Instr. music     Vocal music   \n",
       "3          1.00        Yes                NaN       PENDING       Computers   \n",
       "4          1.00        Yes                NaN       PENDING             NaN   \n",
       "5          0.50        Yes                NaN  Science 6-12         Physics   \n",
       "6          0.50        Yes                NaN  Science 6-12         Physics   \n",
       "7           NaN        NaN                NaN           NaN             NaN   \n",
       "8          0.50         No                NaN           NaN    English 6-12   \n",
       "9          0.50         No                NaN       PENDING             NaN   \n",
       "10          NaN         No                NaN   Physical ed             NaN   \n",
       "11          NaN         No                NaN           NaN  Political sci.   \n",
       "12         0.80         No                NaN   Vocal music         English   \n",
       "13          NaN        NaN                NaN           NaN             NaN   \n",
       "14          NaN        NaN                NaN           NaN             NaN   \n",
       "15          NaN        NaN                NaN           NaN             NaN   \n",
       "16          NaN        NaN                NaN           NaN             NaN   \n",
       "\n",
       "    Certification.2  Unnamed: 11  \n",
       "0               NaN          NaN  \n",
       "1               NaN          NaN  \n",
       "2               NaN          NaN  \n",
       "3               NaN          NaN  \n",
       "4               NaN          NaN  \n",
       "5               NaN          NaN  \n",
       "6               NaN          NaN  \n",
       "7               NaN          NaN  \n",
       "8               NaN          NaN  \n",
       "9               NaN          NaN  \n",
       "10              NaN          NaN  \n",
       "11              NaN          NaN  \n",
       "12              NaN          NaN  \n",
       "13              NaN          NaN  \n",
       "14              NaN          NaN  \n",
       "15              NaN          NaN  \n",
       "16              NaN          NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel(\"dirty_data.xlsx\", engine=\"openpyxl\")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clean up names by removing whitespace, punctuation / symbols, capitalization:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>first_name</th>\n      <th>last_name</th>\n      <th>employee_status</th>\n      <th>subject</th>\n      <th>hire_date</th>\n      <th>%_allocated</th>\n      <th>full_time_</th>\n      <th>do_not_edit!_&gt;</th>\n      <th>certification</th>\n      <th>certification_1</th>\n      <th>certification_2</th>\n      <th>unnamed_11</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>PE</td>\n      <td>39690.0</td>\n      <td>0.75</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>Physical ed</td>\n      <td>Theater</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>Drafting</td>\n      <td>39690.0</td>\n      <td>0.25</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>Physical ed</td>\n      <td>Theater</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Alicia</td>\n      <td>Keys</td>\n      <td>Teacher</td>\n      <td>Music</td>\n      <td>37118.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>Instr. music</td>\n      <td>Vocal music</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Ada</td>\n      <td>Lovelace</td>\n      <td>Teacher</td>\n      <td>NaN</td>\n      <td>27515.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>PENDING</td>\n      <td>Computers</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Desus</td>\n      <td>Nice</td>\n      <td>Administration</td>\n      <td>Dean</td>\n      <td>41431.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>PENDING</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Physics</td>\n      <td>11037.0</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>Science 6-12</td>\n      <td>Physics</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Chemistry</td>\n      <td>11037.0</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>NaN</td>\n      <td>Science 6-12</td>\n      <td>Physics</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>James</td>\n      <td>Joyce</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>32994.0</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>English 6-12</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>Hedy</td>\n      <td>Lamarr</td>\n      <td>Teacher</td>\n      <td>Science</td>\n      <td>27919.0</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>PENDING</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>Carlos</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>Basketball</td>\n      <td>42221.0</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>Physical ed</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>Young</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>NaN</td>\n      <td>34700.0</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Political sci.</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>Micheal</td>\n      <td>Larsen</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>40071.0</td>\n      <td>0.80</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>Vocal music</td>\n      <td>English</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>14</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>15</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>16</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": [
       "      first_name last_name employee_status     subject  hire_date  \\\n",
       "0          Jason    Bourne         Teacher          PE    39690.0   \n",
       "1          Jason    Bourne         Teacher    Drafting    39690.0   \n",
       "2         Alicia      Keys         Teacher       Music    37118.0   \n",
       "3            Ada  Lovelace         Teacher         NaN    27515.0   \n",
       "4          Desus      Nice  Administration        Dean    41431.0   \n",
       "5   Chien-Shiung        Wu         Teacher     Physics    11037.0   \n",
       "6   Chien-Shiung        Wu         Teacher   Chemistry    11037.0   \n",
       "7            NaN       NaN             NaN         NaN        NaN   \n",
       "8          James     Joyce         Teacher     English    32994.0   \n",
       "9           Hedy    Lamarr         Teacher     Science    27919.0   \n",
       "10        Carlos    Boozer           Coach  Basketball    42221.0   \n",
       "11         Young    Boozer           Coach         NaN    34700.0   \n",
       "12       Micheal    Larsen         Teacher     English    40071.0   \n",
       "13           NaN       NaN             NaN         NaN        NaN   \n",
       "14           NaN       NaN             NaN         NaN        NaN   \n",
       "15           NaN       NaN             NaN         NaN        NaN   \n",
       "16           NaN       NaN             NaN         NaN        NaN   \n",
       "\n",
       "    %_allocated full_time_  do_not_edit!_> certification certification_1  \\\n",
       "0          0.75        Yes             NaN   Physical ed         Theater   \n",
       "1          0.25        Yes             NaN   Physical ed         Theater   \n",
       "2          1.00        Yes             NaN  Instr. music     Vocal music   \n",
       "3          1.00        Yes             NaN       PENDING       Computers   \n",
       "4          1.00        Yes             NaN       PENDING             NaN   \n",
       "5          0.50        Yes             NaN  Science 6-12         Physics   \n",
       "6          0.50        Yes             NaN  Science 6-12         Physics   \n",
       "7           NaN        NaN             NaN           NaN             NaN   \n",
       "8          0.50         No             NaN           NaN    English 6-12   \n",
       "9          0.50         No             NaN       PENDING             NaN   \n",
       "10          NaN         No             NaN   Physical ed             NaN   \n",
       "11          NaN         No             NaN           NaN  Political sci.   \n",
       "12         0.80         No             NaN   Vocal music         English   \n",
       "13          NaN        NaN             NaN           NaN             NaN   \n",
       "14          NaN        NaN             NaN           NaN             NaN   \n",
       "15          NaN        NaN             NaN           NaN             NaN   \n",
       "16          NaN        NaN             NaN           NaN             NaN   \n",
       "\n",
       "    certification_2  unnamed_11  \n",
       "0               NaN         NaN  \n",
       "1               NaN         NaN  \n",
       "2               NaN         NaN  \n",
       "3               NaN         NaN  \n",
       "4               NaN         NaN  \n",
       "5               NaN         NaN  \n",
       "6               NaN         NaN  \n",
       "7               NaN         NaN  \n",
       "8               NaN         NaN  \n",
       "9               NaN         NaN  \n",
       "10              NaN         NaN  \n",
       "11              NaN         NaN  \n",
       "12              NaN         NaN  \n",
       "13              NaN         NaN  \n",
       "14              NaN         NaN  \n",
       "15              NaN         NaN  \n",
       "16              NaN         NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.clean_names()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remove entirely empty rows / columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>first_name</th>\n      <th>last_name</th>\n      <th>employee_status</th>\n      <th>subject</th>\n      <th>hire_date</th>\n      <th>%_allocated</th>\n      <th>full_time_</th>\n      <th>certification</th>\n      <th>certification_1</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>PE</td>\n      <td>39690.0</td>\n      <td>0.75</td>\n      <td>Yes</td>\n      <td>Physical ed</td>\n      <td>Theater</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>Drafting</td>\n      <td>39690.0</td>\n      <td>0.25</td>\n      <td>Yes</td>\n      <td>Physical ed</td>\n      <td>Theater</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Alicia</td>\n      <td>Keys</td>\n      <td>Teacher</td>\n      <td>Music</td>\n      <td>37118.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>Instr. music</td>\n      <td>Vocal music</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Ada</td>\n      <td>Lovelace</td>\n      <td>Teacher</td>\n      <td>NaN</td>\n      <td>27515.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>PENDING</td>\n      <td>Computers</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Desus</td>\n      <td>Nice</td>\n      <td>Administration</td>\n      <td>Dean</td>\n      <td>41431.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>PENDING</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Physics</td>\n      <td>11037.0</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>Science 6-12</td>\n      <td>Physics</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Chemistry</td>\n      <td>11037.0</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>Science 6-12</td>\n      <td>Physics</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>James</td>\n      <td>Joyce</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>32994.0</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>English 6-12</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>Hedy</td>\n      <td>Lamarr</td>\n      <td>Teacher</td>\n      <td>Science</td>\n      <td>27919.0</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>PENDING</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>Carlos</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>Basketball</td>\n      <td>42221.0</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>Physical ed</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>Young</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>NaN</td>\n      <td>34700.0</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>Political sci.</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>Micheal</td>\n      <td>Larsen</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>40071.0</td>\n      <td>0.80</td>\n      <td>No</td>\n      <td>Vocal music</td>\n      <td>English</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": [
       "      first_name last_name employee_status     subject  hire_date  \\\n",
       "0          Jason    Bourne         Teacher          PE    39690.0   \n",
       "1          Jason    Bourne         Teacher    Drafting    39690.0   \n",
       "2         Alicia      Keys         Teacher       Music    37118.0   \n",
       "3            Ada  Lovelace         Teacher         NaN    27515.0   \n",
       "4          Desus      Nice  Administration        Dean    41431.0   \n",
       "5   Chien-Shiung        Wu         Teacher     Physics    11037.0   \n",
       "6   Chien-Shiung        Wu         Teacher   Chemistry    11037.0   \n",
       "7          James     Joyce         Teacher     English    32994.0   \n",
       "8           Hedy    Lamarr         Teacher     Science    27919.0   \n",
       "9         Carlos    Boozer           Coach  Basketball    42221.0   \n",
       "10         Young    Boozer           Coach         NaN    34700.0   \n",
       "11       Micheal    Larsen         Teacher     English    40071.0   \n",
       "\n",
       "    %_allocated full_time_ certification certification_1  \n",
       "0          0.75        Yes   Physical ed         Theater  \n",
       "1          0.25        Yes   Physical ed         Theater  \n",
       "2          1.00        Yes  Instr. music     Vocal music  \n",
       "3          1.00        Yes       PENDING       Computers  \n",
       "4          1.00        Yes       PENDING             NaN  \n",
       "5          0.50        Yes  Science 6-12         Physics  \n",
       "6          0.50        Yes  Science 6-12         Physics  \n",
       "7          0.50         No           NaN    English 6-12  \n",
       "8          0.50         No       PENDING             NaN  \n",
       "9           NaN         No   Physical ed             NaN  \n",
       "10          NaN         No           NaN  Political sci.  \n",
       "11         0.80         No   Vocal music         English  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.remove_empty()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rename particular columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>first_name</th>\n      <th>last_name</th>\n      <th>employee_status</th>\n      <th>subject</th>\n      <th>hire_date</th>\n      <th>percent_allocated</th>\n      <th>full_time</th>\n      <th>certification</th>\n      <th>certification_1</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>PE</td>\n      <td>39690.0</td>\n      <td>0.75</td>\n      <td>Yes</td>\n      <td>Physical ed</td>\n      <td>Theater</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>Drafting</td>\n      <td>39690.0</td>\n      <td>0.25</td>\n      <td>Yes</td>\n      <td>Physical ed</td>\n      <td>Theater</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Alicia</td>\n      <td>Keys</td>\n      <td>Teacher</td>\n      <td>Music</td>\n      <td>37118.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>Instr. music</td>\n      <td>Vocal music</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Ada</td>\n      <td>Lovelace</td>\n      <td>Teacher</td>\n      <td>NaN</td>\n      <td>27515.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>PENDING</td>\n      <td>Computers</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Desus</td>\n      <td>Nice</td>\n      <td>Administration</td>\n      <td>Dean</td>\n      <td>41431.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>PENDING</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Physics</td>\n      <td>11037.0</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>Science 6-12</td>\n      <td>Physics</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Chemistry</td>\n      <td>11037.0</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>Science 6-12</td>\n      <td>Physics</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>James</td>\n      <td>Joyce</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>32994.0</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>English 6-12</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>Hedy</td>\n      <td>Lamarr</td>\n      <td>Teacher</td>\n      <td>Science</td>\n      <td>27919.0</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>PENDING</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>Carlos</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>Basketball</td>\n      <td>42221.0</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>Physical ed</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>Young</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>NaN</td>\n      <td>34700.0</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>NaN</td>\n      <td>Political sci.</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>Micheal</td>\n      <td>Larsen</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>40071.0</td>\n      <td>0.80</td>\n      <td>No</td>\n      <td>Vocal music</td>\n      <td>English</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": [
       "      first_name last_name employee_status     subject  hire_date  \\\n",
       "0          Jason    Bourne         Teacher          PE    39690.0   \n",
       "1          Jason    Bourne         Teacher    Drafting    39690.0   \n",
       "2         Alicia      Keys         Teacher       Music    37118.0   \n",
       "3            Ada  Lovelace         Teacher         NaN    27515.0   \n",
       "4          Desus      Nice  Administration        Dean    41431.0   \n",
       "5   Chien-Shiung        Wu         Teacher     Physics    11037.0   \n",
       "6   Chien-Shiung        Wu         Teacher   Chemistry    11037.0   \n",
       "7          James     Joyce         Teacher     English    32994.0   \n",
       "8           Hedy    Lamarr         Teacher     Science    27919.0   \n",
       "9         Carlos    Boozer           Coach  Basketball    42221.0   \n",
       "10         Young    Boozer           Coach         NaN    34700.0   \n",
       "11       Micheal    Larsen         Teacher     English    40071.0   \n",
       "\n",
       "    percent_allocated full_time certification certification_1  \n",
       "0                0.75       Yes   Physical ed         Theater  \n",
       "1                0.25       Yes   Physical ed         Theater  \n",
       "2                1.00       Yes  Instr. music     Vocal music  \n",
       "3                1.00       Yes       PENDING       Computers  \n",
       "4                1.00       Yes       PENDING             NaN  \n",
       "5                0.50       Yes  Science 6-12         Physics  \n",
       "6                0.50       Yes  Science 6-12         Physics  \n",
       "7                0.50        No           NaN    English 6-12  \n",
       "8                0.50        No       PENDING             NaN  \n",
       "9                 NaN        No   Physical ed             NaN  \n",
       "10                NaN        No           NaN  Political sci.  \n",
       "11               0.80        No   Vocal music         English  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.rename_column(\"%_allocated\", \"percent_allocated\").rename_column(\n",
    "    \"full_time_\", \"full_time\"\n",
    ")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Take first non-`NaN` row value in two columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>first_name</th>\n      <th>last_name</th>\n      <th>employee_status</th>\n      <th>subject</th>\n      <th>hire_date</th>\n      <th>percent_allocated</th>\n      <th>full_time</th>\n      <th>certification</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>PE</td>\n      <td>39690.0</td>\n      <td>0.75</td>\n      <td>Yes</td>\n      <td>Physical ed</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>Drafting</td>\n      <td>39690.0</td>\n      <td>0.25</td>\n      <td>Yes</td>\n      <td>Physical ed</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Alicia</td>\n      <td>Keys</td>\n      <td>Teacher</td>\n      <td>Music</td>\n      <td>37118.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>Instr. music</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Ada</td>\n      <td>Lovelace</td>\n      <td>Teacher</td>\n      <td>NaN</td>\n      <td>27515.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>PENDING</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Desus</td>\n      <td>Nice</td>\n      <td>Administration</td>\n      <td>Dean</td>\n      <td>41431.0</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>PENDING</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Physics</td>\n      <td>11037.0</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>Science 6-12</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Chemistry</td>\n      <td>11037.0</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>Science 6-12</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>James</td>\n      <td>Joyce</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>32994.0</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>English 6-12</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>Hedy</td>\n      <td>Lamarr</td>\n      <td>Teacher</td>\n      <td>Science</td>\n      <td>27919.0</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>PENDING</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>Carlos</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>Basketball</td>\n      <td>42221.0</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>Physical ed</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>Young</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>NaN</td>\n      <td>34700.0</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>Political sci.</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>Micheal</td>\n      <td>Larsen</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>40071.0</td>\n      <td>0.80</td>\n      <td>No</td>\n      <td>Vocal music</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": [
       "      first_name last_name employee_status     subject  hire_date  \\\n",
       "0          Jason    Bourne         Teacher          PE    39690.0   \n",
       "1          Jason    Bourne         Teacher    Drafting    39690.0   \n",
       "2         Alicia      Keys         Teacher       Music    37118.0   \n",
       "3            Ada  Lovelace         Teacher         NaN    27515.0   \n",
       "4          Desus      Nice  Administration        Dean    41431.0   \n",
       "5   Chien-Shiung        Wu         Teacher     Physics    11037.0   \n",
       "6   Chien-Shiung        Wu         Teacher   Chemistry    11037.0   \n",
       "7          James     Joyce         Teacher     English    32994.0   \n",
       "8           Hedy    Lamarr         Teacher     Science    27919.0   \n",
       "9         Carlos    Boozer           Coach  Basketball    42221.0   \n",
       "10         Young    Boozer           Coach         NaN    34700.0   \n",
       "11       Micheal    Larsen         Teacher     English    40071.0   \n",
       "\n",
       "    percent_allocated full_time   certification  \n",
       "0                0.75       Yes     Physical ed  \n",
       "1                0.25       Yes     Physical ed  \n",
       "2                1.00       Yes    Instr. music  \n",
       "3                1.00       Yes         PENDING  \n",
       "4                1.00       Yes         PENDING  \n",
       "5                0.50       Yes    Science 6-12  \n",
       "6                0.50       Yes    Science 6-12  \n",
       "7                0.50        No    English 6-12  \n",
       "8                0.50        No         PENDING  \n",
       "9                 NaN        No     Physical ed  \n",
       "10                NaN        No  Political sci.  \n",
       "11               0.80        No     Vocal music  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.coalesce([\"certification\", \"certification_1\"], \"certification\")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert string object rows to categorical to save on memory consumption and speed up access:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "first_name            object\n",
       "last_name             object\n",
       "employee_status       object\n",
       "subject               object\n",
       "hire_date            float64\n",
       "percent_allocated    float64\n",
       "full_time             object\n",
       "certification         object\n",
       "dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "first_name             object\n",
       "last_name              object\n",
       "employee_status      category\n",
       "subject              category\n",
       "hire_date             float64\n",
       "percent_allocated     float64\n",
       "full_time            category\n",
       "certification          object\n",
       "dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.encode_categorical([\"subject\", \"employee_status\", \"full_time\"])\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert Excel date-formatted column to a more interpretable format:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>first_name</th>\n      <th>last_name</th>\n      <th>employee_status</th>\n      <th>subject</th>\n      <th>hire_date</th>\n      <th>percent_allocated</th>\n      <th>full_time</th>\n      <th>certification</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>PE</td>\n      <td>2008-08-30</td>\n      <td>0.75</td>\n      <td>Yes</td>\n      <td>Physical ed</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>Drafting</td>\n      <td>2008-08-30</td>\n      <td>0.25</td>\n      <td>Yes</td>\n      <td>Physical ed</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Alicia</td>\n      <td>Keys</td>\n      <td>Teacher</td>\n      <td>Music</td>\n      <td>2001-08-15</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>Instr. music</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Ada</td>\n      <td>Lovelace</td>\n      <td>Teacher</td>\n      <td>NaN</td>\n      <td>1975-05-01</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>PENDING</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Desus</td>\n      <td>Nice</td>\n      <td>Administration</td>\n      <td>Dean</td>\n      <td>2013-06-06</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>PENDING</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Physics</td>\n      <td>1930-03-20</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>Science 6-12</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Chemistry</td>\n      <td>1930-03-20</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>Science 6-12</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>James</td>\n      <td>Joyce</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>1990-05-01</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>English 6-12</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>Hedy</td>\n      <td>Lamarr</td>\n      <td>Teacher</td>\n      <td>Science</td>\n      <td>1976-06-08</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>PENDING</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>Carlos</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>Basketball</td>\n      <td>2015-08-05</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>Physical ed</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>Young</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>NaN</td>\n      <td>1995-01-01</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>Political sci.</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>Micheal</td>\n      <td>Larsen</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>2009-09-15</td>\n      <td>0.80</td>\n      <td>No</td>\n      <td>Vocal music</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": [
       "      first_name last_name employee_status     subject  hire_date  \\\n",
       "0          Jason    Bourne         Teacher          PE 2008-08-30   \n",
       "1          Jason    Bourne         Teacher    Drafting 2008-08-30   \n",
       "2         Alicia      Keys         Teacher       Music 2001-08-15   \n",
       "3            Ada  Lovelace         Teacher         NaN 1975-05-01   \n",
       "4          Desus      Nice  Administration        Dean 2013-06-06   \n",
       "5   Chien-Shiung        Wu         Teacher     Physics 1930-03-20   \n",
       "6   Chien-Shiung        Wu         Teacher   Chemistry 1930-03-20   \n",
       "7          James     Joyce         Teacher     English 1990-05-01   \n",
       "8           Hedy    Lamarr         Teacher     Science 1976-06-08   \n",
       "9         Carlos    Boozer           Coach  Basketball 2015-08-05   \n",
       "10         Young    Boozer           Coach         NaN 1995-01-01   \n",
       "11       Micheal    Larsen         Teacher     English 2009-09-15   \n",
       "\n",
       "    percent_allocated full_time   certification  \n",
       "0                0.75       Yes     Physical ed  \n",
       "1                0.25       Yes     Physical ed  \n",
       "2                1.00       Yes    Instr. music  \n",
       "3                1.00       Yes         PENDING  \n",
       "4                1.00       Yes         PENDING  \n",
       "5                0.50       Yes    Science 6-12  \n",
       "6                0.50       Yes    Science 6-12  \n",
       "7                0.50        No    English 6-12  \n",
       "8                0.50        No         PENDING  \n",
       "9                 NaN        No     Physical ed  \n",
       "10                NaN        No  Political sci.  \n",
       "11               0.80        No     Vocal music  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.convert_excel_date(\"hire_date\")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Example analysis of the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's perform analysis on the above, cleaned `DataFrame`. First we add some additional, randomly-generated data. Note that we `.copy()` the original to preserve it, given that the following would otherwise modify it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>first_name</th>\n      <th>last_name</th>\n      <th>employee_status</th>\n      <th>subject</th>\n      <th>hire_date</th>\n      <th>percent_allocated</th>\n      <th>full_time</th>\n      <th>certification</th>\n      <th>lucky_number</th>\n      <th>age</th>\n      <th>employee_of_month_count</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>PE</td>\n      <td>2008-08-30</td>\n      <td>0.75</td>\n      <td>Yes</td>\n      <td>Physical ed</td>\n      <td>6</td>\n      <td>74</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Jason</td>\n      <td>Bourne</td>\n      <td>Teacher</td>\n      <td>Drafting</td>\n      <td>2008-08-30</td>\n      <td>0.25</td>\n      <td>Yes</td>\n      <td>Physical ed</td>\n      <td>6</td>\n      <td>90</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Alicia</td>\n      <td>Keys</td>\n      <td>Teacher</td>\n      <td>Music</td>\n      <td>2001-08-15</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>Instr. music</td>\n      <td>2</td>\n      <td>47</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Ada</td>\n      <td>Lovelace</td>\n      <td>Teacher</td>\n      <td>NaN</td>\n      <td>1975-05-01</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>PENDING</td>\n      <td>9</td>\n      <td>76</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Desus</td>\n      <td>Nice</td>\n      <td>Administration</td>\n      <td>Dean</td>\n      <td>2013-06-06</td>\n      <td>1.00</td>\n      <td>Yes</td>\n      <td>PENDING</td>\n      <td>4</td>\n      <td>69</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Physics</td>\n      <td>1930-03-20</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>Science 6-12</td>\n      <td>7</td>\n      <td>61</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>Chien-Shiung</td>\n      <td>Wu</td>\n      <td>Teacher</td>\n      <td>Chemistry</td>\n      <td>1930-03-20</td>\n      <td>0.50</td>\n      <td>Yes</td>\n      <td>Science 6-12</td>\n      <td>4</td>\n      <td>47</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>James</td>\n      <td>Joyce</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>1990-05-01</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>English 6-12</td>\n      <td>9</td>\n      <td>34</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>Hedy</td>\n      <td>Lamarr</td>\n      <td>Teacher</td>\n      <td>Science</td>\n      <td>1976-06-08</td>\n      <td>0.50</td>\n      <td>No</td>\n      <td>PENDING</td>\n      <td>6</td>\n      <td>23</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>Carlos</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>Basketball</td>\n      <td>2015-08-05</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>Physical ed</td>\n      <td>1</td>\n      <td>21</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>Young</td>\n      <td>Boozer</td>\n      <td>Coach</td>\n      <td>NaN</td>\n      <td>1995-01-01</td>\n      <td>NaN</td>\n      <td>No</td>\n      <td>Political sci.</td>\n      <td>1</td>\n      <td>83</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>Micheal</td>\n      <td>Larsen</td>\n      <td>Teacher</td>\n      <td>English</td>\n      <td>2009-09-15</td>\n      <td>0.80</td>\n      <td>No</td>\n      <td>Vocal music</td>\n      <td>5</td>\n      <td>81</td>\n      <td>2</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": [
       "      first_name last_name employee_status     subject  hire_date  \\\n",
       "0          Jason    Bourne         Teacher          PE 2008-08-30   \n",
       "1          Jason    Bourne         Teacher    Drafting 2008-08-30   \n",
       "2         Alicia      Keys         Teacher       Music 2001-08-15   \n",
       "3            Ada  Lovelace         Teacher         NaN 1975-05-01   \n",
       "4          Desus      Nice  Administration        Dean 2013-06-06   \n",
       "5   Chien-Shiung        Wu         Teacher     Physics 1930-03-20   \n",
       "6   Chien-Shiung        Wu         Teacher   Chemistry 1930-03-20   \n",
       "7          James     Joyce         Teacher     English 1990-05-01   \n",
       "8           Hedy    Lamarr         Teacher     Science 1976-06-08   \n",
       "9         Carlos    Boozer           Coach  Basketball 2015-08-05   \n",
       "10         Young    Boozer           Coach         NaN 1995-01-01   \n",
       "11       Micheal    Larsen         Teacher     English 2009-09-15   \n",
       "\n",
       "    percent_allocated full_time   certification  lucky_number  age  \\\n",
       "0                0.75       Yes     Physical ed             6   74   \n",
       "1                0.25       Yes     Physical ed             6   90   \n",
       "2                1.00       Yes    Instr. music             2   47   \n",
       "3                1.00       Yes         PENDING             9   76   \n",
       "4                1.00       Yes         PENDING             4   69   \n",
       "5                0.50       Yes    Science 6-12             7   61   \n",
       "6                0.50       Yes    Science 6-12             4   47   \n",
       "7                0.50        No    English 6-12             9   34   \n",
       "8                0.50        No         PENDING             6   23   \n",
       "9                 NaN        No     Physical ed             1   21   \n",
       "10                NaN        No  Political sci.             1   83   \n",
       "11               0.80        No     Vocal music             5   81   \n",
       "\n",
       "    employee_of_month_count  \n",
       "0                         0  \n",
       "1                         4  \n",
       "2                         0  \n",
       "3                         0  \n",
       "4                         2  \n",
       "5                         3  \n",
       "6                         0  \n",
       "7                         3  \n",
       "8                         0  \n",
       "9                         3  \n",
       "10                        1  \n",
       "11                        2  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_df = cleaned_df.copy().add_columns(\n",
    "    lucky_number=np.random.randint(0, 10, len(cleaned_df)),\n",
    "    age=np.random.randint(10, 100, len(cleaned_df)),\n",
    "    employee_of_month_count=np.random.randint(0, 5, len(cleaned_df)),\n",
    ")\n",
    "\n",
    "data_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate mean, median of all numerical columns after grouping by employee status. Use `.collapse_levels()`, a `pyjanitor` convenience function, to convert the `DataFrame` returned by `.agg()` from having multi-level columns (because we supplied a list of aggregation operations) to single-level by concatenating the level names with an underscore:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>employee_status</th>\n      <th>percent_allocated_mean</th>\n      <th>percent_allocated_median</th>\n      <th>lucky_number_mean</th>\n      <th>lucky_number_median</th>\n      <th>age_mean</th>\n      <th>age_median</th>\n      <th>employee_of_month_count_mean</th>\n      <th>employee_of_month_count_median</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Administration</td>\n      <td>1.000000</td>\n      <td>1.0</td>\n      <td>4</td>\n      <td>4</td>\n      <td>69.000000</td>\n      <td>69</td>\n      <td>2.000000</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Coach</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>1</td>\n      <td>52.000000</td>\n      <td>52</td>\n      <td>2.000000</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Teacher</td>\n      <td>0.644444</td>\n      <td>0.5</td>\n      <td>6</td>\n      <td>6</td>\n      <td>59.222222</td>\n      <td>61</td>\n      <td>1.333333</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": [
       "  employee_status  percent_allocated_mean  percent_allocated_median  \\\n",
       "0  Administration                1.000000                       1.0   \n",
       "1           Coach                     NaN                       NaN   \n",
       "2         Teacher                0.644444                       0.5   \n",
       "\n",
       "   lucky_number_mean  lucky_number_median   age_mean  age_median  \\\n",
       "0                  4                    4  69.000000          69   \n",
       "1                  1                    1  52.000000          52   \n",
       "2                  6                    6  59.222222          61   \n",
       "\n",
       "   employee_of_month_count_mean  employee_of_month_count_median  \n",
       "0                      2.000000                               2  \n",
       "1                      2.000000                               2  \n",
       "2                      1.333333                               0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stats_df = (\n",
    "    data_df.groupby(\"employee_status\")\n",
    "    .agg([\"mean\", \"median\"])\n",
    "    .collapse_levels()\n",
    "    .reset_index()\n",
    ")\n",
    "\n",
    "stats_df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7-final"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
